package dk.aiszone.lang.sql;

import static org.junit.Assert.assertEquals;

import java.util.Date;

import org.junit.Test;

import dk.aiszone.lang.sql.SQLResult.UnknownIdException;
import dk.aiszone.lang.sql.interfaces.SQLJoin;

/**
 * Test cases for the SQL package.
 * 
 * @author Ais
 */
public class SQLTest {

    /**
     * Simple select from multiple tables.
     */
    @Test
    public void testCase01() {
        SQLSelect select = new SQLSelect(new SQLTable("TABLE1"));
        select.addField(new SQLField("icode"));
        
        assertEquals("SELECT TABLE1.icode FROM TABLE1", select.toSQL());
    }

    /**
     * Simple select from multiple tables with constraints.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testCase02() throws UnknownIdException {
        SQLSelect select = new SQLSelect(new SQLTable("TABLE1"));

        SQLField f1, f2;

        select.addField(f1 = new SQLField("icode"));
        select.addField(f2 = new SQLField("icode2"));

        select.addConstraint(new SQLEquals(new SQLAssignableField(f1, new Integer(0))));
        select.addConstraint(new SQLEquals(new SQLAssignableField("t2.icode", f2)));

        select.setValue("t2.icode", "Hejsa");

        assertEquals("SELECT TABLE1.icode, TABLE1.icode2 FROM TABLE1 WHERE TABLE1.icode = 0 AND TABLE1.icode2 = 'Hejsa'", select.toSQL());
    }

    /**
     * Simple select from multiple tables with constraints.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testCase03() throws UnknownIdException {
        SQLSelect select = new SQLSelect(new SQLTable("TABLE1"));

        SQLField f1, f2, f3;

        select.addField(f1 = new SQLField("icode"));
        select.addField(f2 = new SQLField("icode2"));

        select.addConstraint(new SQLEquals(new SQLAssignableField(f1, new Integer(0))));
        select.addConstraint(new SQLLike(new SQLAssignableField("t2.icode", f2)));

        SQLJoin join = new SQLInnerJoin(new SQLTable("TABLE3", "PERSON"));
        join.addField(f3 = new SQLField("icode", "PrimaryKey"));

        SQLOr or = new SQLOr(new SQLEquals(new SQLAssignableField(f1, new SQLConstraintField(join.getJoinedTable(), "joinme"))));
        or.addConstraint(new SQLEquals(new SQLAssignableField(f3, new Integer(3))));
        join.addConstraint(or);

        select.addJoin(join);

        select.setValue("t2.icode", "Hejsa");

        assertEquals("SELECT TABLE1.icode, TABLE1.icode2, PERSON.icode AS PrimaryKey FROM TABLE1 INNER JOIN TABLE3 AS PERSON ON (((TABLE1.icode = PERSON.joinme) OR (PERSON.PrimaryKey = 3))) WHERE TABLE1.icode = 0 AND TABLE1.icode2 LIKE 'Hejsa'", select.toSQL());
    }

    /**
     * Test validating that unset constraints are not included in the generated result.
     */
    @Test
    public void testCase04() {
        SQLSelect select = new SQLSelect(new SQLTable("TABLE1"));

        SQLField f1, f2;

        select.addField(f1 = new SQLField("icode"));
        select.addField(f2 = new SQLField("icode2"));

        select.addConstraint(new SQLEquals(new SQLAssignableField(f1, new Integer(0))));
        select.addConstraint(new SQLEquals(new SQLAssignableField("t2.icode", f2)));

        assertEquals("SELECT TABLE1.icode, TABLE1.icode2 FROM TABLE1 WHERE TABLE1.icode = 0", select.toSQL());
    }

    /**
     * Test validating that unset constraints are not included in the generated result.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testCase05() throws UnknownIdException {
        SQLSelect select = new SQLSelect(new SQLTable("TABLE1"));

        SQLField f1, f2;

        select.addField(f1 = new SQLField("icode"));
        select.addField(f2 = new SQLField("icode2"));

        select.addConstraint(new SQLEquals(new SQLAssignableField("t1.icode", f1)));
        select.addConstraint(new SQLEquals(new SQLAssignableField("t2.icode", f2)));

        select.setValue("t2.icode", "Hejsa");

        assertEquals("SELECT TABLE1.icode, TABLE1.icode2 FROM TABLE1 WHERE TABLE1.icode2 = 'Hejsa'", select.toSQL());
    }

    /**
     * Test validating that unset constraints are not included in the generated result - also when included in Or statements.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testCase06() throws UnknownIdException {
        SQLSelect select = new SQLSelect(new SQLTable("TABLE1"));

        SQLField f1, f2;

        select.addField(f1 = new SQLField("icode"));
        select.addField(f2 = new SQLField("icode2"));

        SQLOr or = new SQLOr(new SQLEquals(new SQLAssignableField("t1.icode", f1)));
        or.addConstraint(new SQLEquals(new SQLAssignableField("t2.icode", f2)));
        select.addConstraint(or);

        select.setValue("t2.icode", "Hejsa");

        assertEquals("SELECT TABLE1.icode, TABLE1.icode2 FROM TABLE1 WHERE TABLE1.icode2 = 'Hejsa'", select.toSQL());
    }

    /**
     * Simple select from multiple tables with constraints.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testCase08() throws UnknownIdException {
        SQLSelect select = new SQLSelect(new SQLTable("TABLE1"));
        select.setDistinct(true);
        select.addField(new SQLField("project"));

        select.addConstraint(new SQLEquals(new SQLAssignableField(new SQLConstraintField(select.getTable(), "deleted"), null)));

        assertEquals("SELECT DISTINCT TABLE1.project FROM TABLE1 WHERE TABLE1.deleted is null", select.toSQL());
    }

    /**
     * Simple select from multiple tables with constraints.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testCase09() throws UnknownIdException {
        SQLSelect select = new SQLSelect(new SQLTable("TABLE1"));

        SQLField f1, f2;

        select.addField(f1 = new SQLField("icode"));
        select.addField(f2 = new SQLField("icode2"));

        SQLOr or = new SQLOr(new SQLEquals(new SQLAssignableField("t1.icode", f1)));
        or.addConstraint(new SQLEquals(new SQLAssignableField("t2.icode", f2)));
        select.addConstraint(or);

        select.setValue("t2.icode", "Hejsa");

        assertEquals("SELECT TABLE1.icode, TABLE1.icode2 FROM TABLE1 WHERE TABLE1.icode2 = ?", select.toPreparedStatement());
    }

    /**
     * Insert statement test.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testInsert01() throws UnknownIdException {
        SQLInsert insert = new SQLInsert(new SQLTable("TABLE1"));

        insert.addField(new SQLAssignableField("icode", new SQLField("icode")));
        insert.addField(new SQLAssignableField("timestamp", new SQLField("timestamp")));

        insert.setValue("icode", new Integer(0));
        insert.setValue("timestamp", new Integer(1010));

        assertEquals("INSERT INTO TABLE1 (icode, timestamp) VALUES (0, 1010)", insert.toSQL());
    }

    /**
     * Insert statement test.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testInsert02() throws UnknownIdException {
        SQLInsert insert = new SQLInsert(new SQLTable("TABLE1"));

        insert.addField(new SQLAssignableField("icode", new SQLField("icode")));
        insert.addField(new SQLAssignableField("timestamp", new SQLField("timestamp")));

        insert.setValue("timestamp", new Integer(1010));

        assertEquals("INSERT INTO TABLE1 (timestamp) VALUES (1010)", insert.toSQL());
    }

    /**
     * Insert statement test.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testInsert03() throws UnknownIdException {
        SQLInsert insert = new SQLInsert(new SQLTable("TABLE1"));

        insert.addField(new SQLAssignableField("icode", new SQLField("icode")));
        insert.addField(new SQLAssignableField("timestamp", new SQLField("timestamp")));

        insert.setValue("icode", new Integer(1));
        insert.setValue("timestamp", new Integer(1010));

        assertEquals("INSERT INTO TABLE1 (icode, timestamp) VALUES (?, ?)", insert.toPreparedStatement());
    }

    /**
     * Update statement test.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testUpdate01() throws UnknownIdException {
        SQLUpdate update = new SQLUpdate(new SQLTable("TABLE1"));

        update.addField(new SQLAssignableField("icode", new SQLField("icode")));
        update.addField(new SQLAssignableField("timestamp", new SQLField("timestamp")));

        update.setValue("icode", new Integer(0));
        update.setValue("timestamp", new Integer(1010));

        assertEquals("UPDATE TABLE1 SET icode = 0, timestamp = 1010", update.toSQL());
    }

    /**
     * Update statement test.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testUpdate02() throws UnknownIdException {
        SQLUpdate update = new SQLUpdate(new SQLTable("TABLE1"));

        update.addField(new SQLAssignableField("icode", new SQLField("icode")));
        update.addField(new SQLAssignableField("timestamp", new SQLField("timestamp")));

        update.setValue("timestamp", new Integer(1010));

        assertEquals("UPDATE TABLE1 SET timestamp = 1010", update.toSQL());
    }

    /**
     * Update statement test.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testUpdate03() throws UnknownIdException {
        SQLUpdate update = new SQLUpdate(new SQLTable("TABLE1"));

        update.addField(new SQLAssignableField("icode", new SQLField("icode")));
        update.addField(new SQLAssignableField("timestamp", new SQLField("timestamp")));
        update.addConstraint(new SQLEquals(new SQLAssignableField("color", new SQLField("color"))));

        update.setValue("icode", new Integer(0));
        update.setValue("timestamp", new Integer(1010));

        assertEquals("UPDATE TABLE1 SET icode = 0, timestamp = 1010", update.toSQL());
    }

    /**
     * Update statement test.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testUpdate04() throws UnknownIdException {
        SQLUpdate update = new SQLUpdate(new SQLTable("TABLE1"));

        update.addField(new SQLAssignableField("icode", new SQLField("icode")));
        update.addField(new SQLAssignableField("timestamp", new SQLField("timestamp")));
        update.addConstraint(new SQLEquals(new SQLAssignableField("color", new SQLField("color"))));

        update.setValue("icode", new Integer(0));
        update.setValue("timestamp", new Integer(1010));

        update.setValue("color", "Red");

        assertEquals("UPDATE TABLE1 SET icode = 0, timestamp = 1010 WHERE color = 'Red'", update.toSQL());
    }

    /**
     * Update statement test.
     * 
     * @throws UnknownIdException
     */
    @Test
    public void testUpdate05() throws UnknownIdException {
        SQLUpdate update = new SQLUpdate(new SQLTable("TABLE1"));

        update.addField(new SQLAssignableField("icode", new SQLField("icode")));
        update.addField(new SQLAssignableField("timestamp", new SQLField("timestamp")));
        update.addConstraint(new SQLEquals(new SQLAssignableField("color", new SQLField("color"))));

        update.setValue("icode", new Integer(1));
        update.setValue("timestamp", new Date());
        update.setValue("color", "red");

        assertEquals("UPDATE TABLE1 SET icode = ?, timestamp = ? WHERE color = ?", update.toPreparedStatement());
    }

}